package iedcyt.acute.dataAccess.dao.acute1;

import iedcyt.acute.dataAccess.domain.acute1.CollGroup;
import iedcyt.acute.dataAccess.generic.SpringDao;
import iedcyt.acute.dataAccess.idao.acute1.ICollGroupDao;

import java.util.ArrayList;
import java.util.List;



public class CollGroupDao extends SpringDao implements ICollGroupDao  {

	@Override
	public void create() {
		//comprobamos si la tabla existe y si no la creamos
		try {
			_jdbcTemplate.queryForInt("select count(*) from CollGroup");
		} catch (Exception e) {
			_jdbcTemplate.execute("CREATE TABLE CollGroup (idgr numeric, name varchar(50))");
			_jdbcTemplate.execute("CREATE INDEX idxCollGroup1 on CollGroup (idGr) WITH PRIMARY");
			_jdbcTemplate.execute("CREATE INDEX idxCollGroup2 on CollGroup (Name)");
		}
	}
	
	@Override
	public List<CollGroup> selectAll() {
		
		return _jdbcTemplate.query("SELECT * FROM CollGroup",
				new CollGroupRowMapper());
	}
	
	@Override
	public void deleteAll() {
		
		_jdbcTemplate.update("DELETE from CollGroup");
	}

	

	@Override
	public void insert(CollGroup doc) {
	
		_jdbcTemplate.update("INSERT INTO CollGroup (idGr, Name) VALUES (?,?)",
				new Object[] { doc.get_idGr(), doc.get_Name() });
		
	}
	
	@Override
	public void update(CollGroup doc) {
	
		_jdbcTemplate.update("UPDATE CollGroup SET Name = ? WHERE idGr = ?",
				new Object[] { doc.get_Name(), doc.get_idGr() });
		
	}

	@Override
	public List<CollGroup> select(int[] ids) {
		
		if (ids.length != 0)
		{
		String where = "idGr = ? ";
		int cnt = 1;
		for (int i = 0; i < ids.length -1 ; i++)
		{
			cnt++;
			where += "OR idGr = ? ";
		}
		
		Object[] objs = new Object[cnt];
		
		for (int i = 0; i < ids.length ; i++)
		{
			objs[i] = ids[i];
		}
		
		return _jdbcTemplate
				.query(
						"SELECT * FROM CollGroup WHERE " + where,
						objs,
						new CollGroupRowMapper());
		}
		else return new ArrayList<CollGroup>();
	
	}

	
	@Override
	public void delete(int id) {
	
		_jdbcTemplate.update("DELETE from CollGroup WHERE idGr=?",
				new Object[] { id  });
		
	}

	@Override
	public int selectMaxId() {
		return _jdbcTemplate.queryForInt("SELECT MAX(idGr) FROM CollGroup");
	}

	@Override
	public List<CollGroup> selectByIdAu(int[] idsAu) {
		
		if (idsAu.length != 0)
		{
		String where = "cGM.idAu = ? ";
		int cnt = 1;
		for (int i = 0; i < idsAu.length -1 ; i++)
		{
			cnt++;
			where += "OR cGM.idAu = ? ";
		}
		
		Object[] objs = new Object[cnt];
		
		for (int i = 0; i < idsAu.length ; i++)
		{
			objs[i] = idsAu[i];
		}
		
		return _jdbcTemplate
				.query(
						"SELECT DISTINCT cG.* FROM CollGroup_Member cGM INNER JOIN CollGroup cG ON cGM.idGr = cG.idGr WHERE " + where,
						objs,
						new CollGroupRowMapper());
		}
		else return new ArrayList<CollGroup>();
	}
	
	@Override
	public List<CollGroup> selectBySign(String sign, boolean like) 
	{
		if (like) sign = "%" + sign + "%";
		
		return _jdbcTemplate
				.query(
						"SELECT DISTINCT cG.* FROM Sign sg INNER JOIN " +
							"(Author_Sign auSg INNER JOIN " +
								"(CollGroup_Member cGM INNER JOIN CollGroup cG ON cGM.idGr = cG.idGr) " +
							" ON cGM.idAu = auSg.idAu) " +
						"ON auSg.idSign = sg.idSign WHERE sg.Sign LIKE ?",
						new Object[] { sign },
						new CollGroupRowMapper());
		
	}

}

